// import mysql2 from "mysql2/promise";
import fs from "node:fs";
import jsyaml from "js-yaml";
import express from "express";
import knex from "knex";
const yaml = fs.readFileSync("./db.config.yaml", "utf8");
const config = jsyaml.load(yaml);
//使用knex 代替 mysql2
const db = knex({
  client: "mysql2",
  connection: config.db,
});
//创建表 knex所有代码直接编写没有效果 必须.then接收
db.schema
  .createTableIfNotExists("message", (table) => {
    table.increments("id").primary(); //id主键 自增
    table.string("title"); //标题
    table.integer("status").defaultTo(2); //状态
    table.string("content"); //内容
    table.enu("type", ["list", "dialog"]).defaultTo("list");
    table.timestamps(true, true); //创建时间 更新时间
  })
  .then(() => {
    console.log("创建表成功");
  });
//事务
db.transaction(async (trx) => {
  try {
    //a-100 b+100
    //保持原子的一致性 ，要么都成功，要么都回滚
    await trx("list").update({ money: -100 }).where({ id: 1 }); //A
    await trx("list").update({ money: +100 }).where({ id: 2 }); //B
    await trx.commit(); //提交事务
  } catch {
    await trx.rollback(); //回滚事务
  }
})
  .then(() => {})
  .catch(() => {});
// const sql = await mysql2.createConnection({ ...config.db })
const app = express();
app.use(express.json()); //支持json格式 post请求
//查询 全部
app.get("/", async (req, res) => {
  const data = await db("message").select("*").orderBy("created_at", "desc"); //db.row(select * from message)
  //支持sql语句
  //   db.raw(`select * from message`).then((data) => console.log(data));
  const count = await db("message").count("* as total");
  //   const [data] = await sql.query("select * from user");
  //连表
  //   const list = await db("user")
  //     .select("*")
  //     .leftJoin("admin", "admin.user_id", "user.id");
  res.json({
    data,
    // list,
    count: count[0].total,
    sql: db("message").select().toSQL().sql, //翻译成sql语句
  });
  console.log("list: ", list);
});
//单个查询
app.get("/message/:id", async (req, res) => {
  const row = await db("message").select("*").where({ id: req.params.id });
  //   const [row] = await sql.query(
  //     // `select * from user where id = ${req.params.id}`
  //     `select * from user where id = ?`,
  //     [req.params.id]
  //   );
  res.send(row);
});
//新增
app.post("/create", async (req, res) => {
  const { title, content } = req.body;
  await db("message").insert({ title, content });
  //   await sql.query(
  //     `insert into user ( title, content) values(?,?)`,
  //     [title, content]
  //   );
  res.send({ code: 200 });
});
//修改
app.post("/update", async (req, res) => {
  const { title, content, type, status, id } = req.body;
  await db("message").update({ title, content, type, status }).where({ id });
  //   await sql.query(
  //     `update user set title = ?, content = ?, type = ?, status = ? where id = ?`,
  //     [title, content, type, status, id]
  //   );
  res.send({ code: 200 });
});
//删除
app.post("/delete", async (req, res) => {
  const { id } = req.body;
  await db("message").delete().where({ id });
  //   await sql.query(`delete from user where id = ?`, [id]);
  res.send({ code: 200 });
});
app.listen(3001, () => {
  console.log("server started ");
});
